﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ScriptGenerator;
using System.Runtime.InteropServices;

namespace CEQuery
{

    public partial class KeyManipulation : Form
    {
        public string ConnectionString { get; set; }
        public string TableName { get; set; }
        public string ConstraintScript { get; private set; }
        public List<CETable> CETables { get; set; }

        public const int TVIF_STATE = 0x8;
        public const int TVIS_STATEIMAGEMASK = 0xF000;
        public const int TV_FIRST = 0x1100;
        public const int TVM_SETITEM = TV_FIRST + 63;
        public struct TVITEM
        {
            public int mask;
            public IntPtr hItem;
            public int state;
            public int stateMask;
            [MarshalAs(UnmanagedType.LPTStr)]
            public String lpszText;
            public int cchTextMax;
            public int iImage;
            public int iSelectedImage;
            public int cChildren;
            public IntPtr lParam;
        }

        [DllImport("user32.dll")]
        static extern IntPtr SendMessage(IntPtr hWnd, uint Msg, IntPtr wParam, IntPtr lParam);

        private List<global::ScriptGenerator.Constraint> fks;
        private List<global::ScriptGenerator.Constraint> uks;
        private List<global::ScriptGenerator.Index> indexes;

        public KeyManipulation()
        {
            InitializeComponent();
        }

        private void KeyManipulation_Load(object sender, EventArgs e)
        {
            try
            {
                grpUk.Visible = false;
                grpFk.Visible = false;
                lblHeader.Text = lblHeader.Text += TableName;

                if (!string.IsNullOrEmpty(ConnectionString) && !string.IsNullOrEmpty(TableName))
                {
                    IRepository repository = new DBRepository(ConnectionString);
                    fks = repository.GetAllForeignKeys();
                    uks = repository.GetUniqueKeysFromTable(TableName);
                    indexes = repository.GetIndexesFromTable(TableName);

                    TreeNode fkNode = new TreeNode("Foreign Keys");
                    fkNode.ImageIndex = 0;
                    tvKeys.Nodes.Add(fkNode);
                    fks.FindAll(j => j.ConstraintTableName == TableName).Select(f => f.ConstraintName).Distinct().ToList().ForEach(delegate(string constraintName)
                    {
                        TreeNode childNode = new TreeNode();
                        childNode.ImageIndex = 1;
                        childNode.SelectedImageIndex = 1;
                        childNode.Text = constraintName;
                        fkNode.Nodes.Add(childNode);
                    });
                    HideCheckBox(fkNode);

                    TreeNode ukNode = new TreeNode("Unique Keys");
                    ukNode.ImageIndex = 0;
                    tvKeys.Nodes.Add(ukNode);
                    uks.Select(u => u.ConstraintName).Distinct().ToList().ForEach(delegate(string constraintName)
                    {
                        TreeNode childNode = new TreeNode();
                        childNode.ImageIndex = 1;
                        childNode.SelectedImageIndex = 1;
                        childNode.Text = constraintName;
                        ukNode.Nodes.Add(childNode);
                    });
                    HideCheckBox(ukNode);

                    TreeNode indexNode = new TreeNode("Indexes");
                    indexNode.ImageIndex = 0;
                    tvKeys.Nodes.Add(indexNode);
                    indexes.Select(i => i.IndexName).Distinct().ToList().ForEach(delegate(string indexName)
                    {
                        TreeNode childNode = new TreeNode();
                        childNode.ImageIndex = 1;
                        childNode.SelectedImageIndex = 1;
                        childNode.Text = indexName;
                        indexNode.Nodes.Add(childNode);
                    });
                    HideCheckBox(indexNode);

                    //Populate Combos
                    CETables.Select(s => s.TableName).ToList().ForEach(delegate(string table) { cboBaseTable.Items.Add(table); });
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void tvKeys_MouseUp(object sender, MouseEventArgs e)
        {
            try
            {
                if (tvKeys.GetNodeAt(e.X, e.Y) != null && tvKeys.GetNodeAt(e.X, e.Y).Nodes.Count == 0 && tvKeys.GetNodeAt(e.X, e.Y).Parent != null)
                {
                    TreeNode node = tvKeys.GetNodeAt(e.X, e.Y);
                    switch (node.Parent.Text)
                    {
                        case "Foreign Keys":
                            grpFk.Visible = true;
                            grpUk.Visible = false;

                            txtFkName.Text = node.Text;
                            txtFkName.ReadOnly = true;

                            cboBaseTable.SelectedIndex = -1;

                            var fkRows = from f in fks
                                         where f.ConstraintName == node.Text && f.ConstraintTableName.Length > 0
                                         orderby f.OrdinalPosition
                                         select f;
                            var refRows = from u in fks
                                          where u.ConstraintName == node.Text && u.UniqueConstraintTableName.Length > 0
                                          orderby u.OrdinalPosition
                                          select u;
                            cboBaseTable.SelectedItem = refRows.FirstOrDefault().UniqueConstraintTableName;
                            dgFks.Rows.Clear();

                            fkRows.ToList().ForEach(delegate(global::ScriptGenerator.Constraint constraint)
                            {
                                dgFks.Rows.Add();
                                ((DataGridViewComboBoxCell)dgFks.Rows[dgFks.Rows.Count - 2].Cells[0]).Value = refRows.ToList().FirstOrDefault(r => r.OrdinalPosition == constraint.OrdinalPosition).UniqueColumnName;
                                ((DataGridViewComboBoxCell)dgFks.Rows[dgFks.Rows.Count - 2].Cells[1]).Value = constraint.ColumnName;

                            });

                            break;
                        case "Unique Keys":
                            grpFk.Visible = false;
                            grpUk.Visible = true;
                            chkUnique.Checked = true;
                            chkUnique.Enabled = false;
                            rdUk.Checked = true;
                            rdIndex.Enabled = false;
                            rdUk.Enabled = false;

                            txtUkName.Text = node.Text;
                            txtUkName.ReadOnly = true;

                            dgUks.Rows.Clear();
                            dgUks.Columns[1].Visible = false;
                            uks.FindAll(u => u.ConstraintName == node.Text).ForEach(delegate(global::ScriptGenerator.Constraint constraint)
                            {
                                dgUks.Rows.Add();
                                ((DataGridViewComboBoxCell)dgUks.Rows[dgUks.Rows.Count - 2].Cells[0]).Value = constraint.ColumnName;

                            });

                            break;
                        case "Indexes":
                            grpFk.Visible = false;
                            grpUk.Visible = true;
                            chkUnique.Checked = false;
                            chkUnique.Enabled = true;
                            rdIndex.Checked = true;
                            rdIndex.Enabled = false;
                            rdUk.Enabled = false;

                            txtUkName.Text = node.Text;
                            txtUkName.ReadOnly = true;

                            dgUks.Rows.Clear();
                            dgUks.Columns[1].Visible = true;
                            indexes.FindAll(i => i.IndexName == node.Text).ForEach(delegate(global::ScriptGenerator.Index index)
                            {
                                dgUks.Rows.Add();
                                ((DataGridViewComboBoxCell)dgUks.Rows[dgUks.Rows.Count - 2].Cells[0]).Value = index.ColumnName;
                                ((DataGridViewComboBoxCell)dgUks.Rows[dgUks.Rows.Count - 2].Cells[1]).Value = index.SortOrder == SortOrderEnum.Asc ? "ASC" : "DESC";
                                chkUnique.Checked = index.Unique;
                            });
                            break;
                        default:
                            break;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void HideCheckBox(TreeNode node)
        {
            TVITEM tvi = new TVITEM();
            tvi.hItem = node.Handle;
            tvi.mask = TVIF_STATE;
            tvi.stateMask = TVIS_STATEIMAGEMASK;
            tvi.state = 0;
            IntPtr lparam = Marshal.AllocHGlobal(Marshal.SizeOf(tvi)); Marshal.StructureToPtr(tvi, lparam, false);
            SendMessage(tvKeys.Handle, TVM_SETITEM, IntPtr.Zero, lparam);
        }

        private void cboBaseTable_SelectionChangeCommitted(object sender, EventArgs e)
        {
            try
            {
                if (cboBaseTable.SelectedIndex > -1)
                {
                    dgFks.Rows.Clear();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void dgUks_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
        {
            try
            {
                if (((DataGridViewComboBoxCell)dgUks.Rows[e.RowIndex].Cells[0]).Items.Count == 0)
                {
                    CETables.FirstOrDefault(c => c.TableName == TableName).Columns.ForEach(delegate(string column)
                    {
                        ((DataGridViewComboBoxCell)dgUks.Rows[e.RowIndex].Cells[0]).Items.Add(column);
                    });
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void dgUks_CellValidated(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (dgUks.Rows.Count > 2 && e.ColumnIndex == 0)
                {
                    foreach (DataGridViewRow dr in dgUks.Rows)
                    {
                        if (dr.Index != e.RowIndex && dr.Cells[e.ColumnIndex].Value != null && dgUks.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
                        {
                            if (dr.Cells[e.ColumnIndex].Value.ToString() == dgUks.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString())
                            {
                                dgUks.Rows[e.RowIndex].Cells[e.ColumnIndex].ErrorText = "Column already in use";
                                break;
                            }
                            else
                            {
                                dgUks.Rows[e.RowIndex].Cells[e.ColumnIndex].ErrorText = string.Empty;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void dgFks_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
        {
            try
            {
                if (cboBaseTable.SelectedIndex > -1)
                {
                    if (((DataGridViewComboBoxCell)dgFks.Rows[e.RowIndex].Cells[0]).Items.Count == 0)
                    {
                        CETables.FirstOrDefault(c => c.TableName == cboBaseTable.SelectedItem.ToString()).Columns.ForEach(delegate(string column)
                        {
                            ((DataGridViewComboBoxCell)dgFks.Rows[e.RowIndex].Cells[0]).Items.Add(column);
                        });
                    }
                    if (((DataGridViewComboBoxCell)dgFks.Rows[e.RowIndex].Cells[1]).Items.Count == 0)
                    {
                        CETables.FirstOrDefault(c => c.TableName == TableName).Columns.ForEach(delegate(string column)
                        {
                            ((DataGridViewComboBoxCell)dgFks.Rows[e.RowIndex].Cells[1]).Items.Add(column);
                        });
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void dgFks_CellValidated(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (dgFks.Rows.Count > 2)
                {
                    foreach (DataGridViewRow dr in dgFks.Rows)
                    {
                        if (dr.Index != e.RowIndex && dr.Cells[e.ColumnIndex].Value != null && dgFks.Rows[e.RowIndex].Cells[e.ColumnIndex].Value != null)
                        {
                            if (dr.Cells[e.ColumnIndex].Value.ToString() == dgFks.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString())
                            {
                                dgFks.Rows[e.RowIndex].Cells[e.ColumnIndex].ErrorText = "Column already in use";
                                break;
                            }
                            else
                            {
                                dgFks.Rows[e.RowIndex].Cells[e.ColumnIndex].ErrorText = string.Empty;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void dgUks_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
        {
            try
            {
                if (((DataGridViewComboBoxCell)dgUks.Rows[e.RowIndex].Cells[0]).Items.Count == 0)
                {
                    CETables.FirstOrDefault(c => c.TableName == TableName).Columns.ForEach(delegate(string column)
                    {
                        ((DataGridViewComboBoxCell)dgUks.Rows[e.RowIndex].Cells[0]).Items.Add(column);
                    });
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void dgFks_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
        {
            try
            {
                if (cboBaseTable.SelectedIndex > -1)
                {
                    if (((DataGridViewComboBoxCell)dgFks.Rows[e.RowIndex].Cells[0]).Items.Count == 0)
                    {
                        CETables.FirstOrDefault(c => c.TableName == cboBaseTable.SelectedItem.ToString()).Columns.ForEach(delegate(string column)
                        {
                            ((DataGridViewComboBoxCell)dgFks.Rows[e.RowIndex].Cells[0]).Items.Add(column);
                        });
                    }
                    if (((DataGridViewComboBoxCell)dgFks.Rows[e.RowIndex].Cells[1]).Items.Count == 0)
                    {
                        CETables.FirstOrDefault(c => c.TableName == TableName).Columns.ForEach(delegate(string column)
                        {
                            ((DataGridViewComboBoxCell)dgFks.Rows[e.RowIndex].Cells[1]).Items.Add(column);
                        });
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnModify_Click(object sender, EventArgs e)
        {
            try
            {
                bool hasError = false;
                bool hasValidColumns = false;
                StringBuilder sbScript = new StringBuilder();
                string columns = string.Empty;
                string baseColumns = string.Empty;

                if (grpUk.Visible || grpFk.Visible)
                {
                    if (grpFk.Visible)
                    {
                        if (txtFkName.Text == string.Empty)
                        {
                            errProvider.SetError(txtFkName, "Constraint Name can not be empty.");
                            hasError = true;
                        }
                        else
                        {
                            errProvider.SetError(txtFkName, string.Empty);
                            hasError = false;
                        }
                        if (cboBaseTable.SelectedIndex < 0)
                        {
                            errProvider.SetError(cboBaseTable, "Primary Key table can not be empty.");
                            hasError = true;
                        }
                        else
                        {
                            errProvider.SetError(cboBaseTable, string.Empty);
                            hasError = false;
                        }
                        foreach (DataGridViewRow row in dgFks.Rows)
                        {
                            foreach (DataGridViewCell cell in row.Cells)
                            {
                                if (cell.ErrorText != string.Empty)
                                {
                                    hasError = true;
                                    break;
                                }
                            }
                        }

                        if (!hasError)
                        {
                            foreach (DataGridViewRow row in dgFks.Rows)
                            {
                                if (!row.IsNewRow)
                                {
                                    if (row.Cells[0].Value != null && row.Cells[1].Value != null)
                                    {
                                        hasValidColumns = true;
                                        baseColumns += "[" + row.Cells[0].Value.ToString() + "],";
                                        columns += "[" + row.Cells[1].Value.ToString() + "],";
                                    }
                                }
                            }

                            if (hasValidColumns)
                            {
                                if (txtFkName.ReadOnly)
                                {
                                    sbScript.AppendFormat("ALTER TABLE [{0}] DROP CONSTRAINT [{1}] {2}", TableName, txtFkName.Text, Environment.NewLine);
                                }
                                sbScript.AppendFormat("ALTER TABLE [{0}] ADD CONSTRAINT [{1}] FOREIGN KEY ({2}) REFERENCES [{3}] ({4}) {5}", TableName, txtFkName.Text, columns.Substring(0, columns.Length - 1), cboBaseTable.SelectedItem.ToString(), baseColumns.Substring(0, baseColumns.Length - 1), Environment.NewLine);
                            }
                        }
                    }
                    else
                    {
                        if (txtUkName.Text == string.Empty)
                        {
                            errProvider.SetError(txtUkName, "Constraint Name can not be empty.");
                            hasError = true;
                        }
                        else
                        {
                            errProvider.SetError(txtUkName, string.Empty);
                            hasError = false;
                        }
                        if (!rdIndex.Checked && !rdUk.Checked)
                        {
                            errProvider.SetError(rdIndex, "Select either Unique Key or Index.");
                            hasError = true;
                        }
                        else
                        {
                            errProvider.SetError(rdIndex, string.Empty);
                            hasError = false;
                        }
                        foreach (DataGridViewRow row in dgUks.Rows)
                        {
                            foreach (DataGridViewCell cell in row.Cells)
                            {
                                if (cell.ErrorText != string.Empty)
                                {
                                    hasError = false;
                                    break;
                                }
                            }
                        }
                        if (!hasError)
                        {
                            foreach (DataGridViewRow row in dgUks.Rows)
                            {
                                if (!row.IsNewRow)
                                {
                                    if (rdIndex.Checked)
                                    {
                                        if (row.Cells[0].Value != null && row.Cells[1].Value != null)
                                        {
                                            hasValidColumns = true;
                                            columns += "[" + row.Cells[0].Value.ToString() + "] " + row.Cells[1].Value.ToString() + ",";
                                        }
                                    }
                                    else
                                    {
                                        if (row.Cells[0].Value != null && row.Cells[1].Value != null)
                                        {
                                            hasValidColumns = true;
                                            columns += "[" + row.Cells[0].Value.ToString() + "],";
                                        }
                                    }
                                }
                            }
                            if (hasValidColumns)
                            {
                                if (txtUkName.ReadOnly)
                                {
                                    sbScript.AppendFormat("ALTER TABLE [{0}] DROP {1} [{2}] {3}", TableName, rdIndex.Checked ? "INDEX" : "CONSTRAINT", txtUkName.Text, Environment.NewLine);
                                }
                                if (rdIndex.Checked)
                                {
                                    sbScript.Append("CREATE ");

                                    if (chkUnique.Checked)
                                        sbScript.Append("UNIQUE ");
                                    sbScript.AppendFormat("INDEX [{0}] ON [{1}] ({2}) {3}", txtUkName.Text, TableName, columns.Substring(0, columns.Length - 1), Environment.NewLine);
                                }
                                else
                                {
                                    sbScript.AppendFormat("ALTER TABLE [{0}] ADD CONSTRAINT [{1}] UNIQUE NONCLUSTERED ({2}) {3}", TableName, txtUkName.Text, columns.Substring(0, columns.Length - 1), Environment.NewLine);
                                }
                            }
                        }
                    }
                    if (sbScript.Length > 0)
                    {
                        ConstraintScript = sbScript.ToString();
                        btnOk.PerformClick();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnAddFk_Click(object sender, EventArgs e)
        {            
            grpFk.Visible = true;
            grpUk.Visible = false;
            txtFkName.Text = string.Empty;
            txtFkName.ReadOnly = false;
            cboBaseTable.SelectedIndex = -1;
            dgFks.Rows.Clear();

            btnAddFk.Enabled = false;
            btnAddUk.Enabled = false;           
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            try
            {
                StringBuilder sbScript = new StringBuilder();
                foreach (TreeNode node in tvKeys.Nodes)
                {
                    if (node.Text == "Foreign Keys" || node.Text == "Unique Keys")
                    {
                        foreach (TreeNode childNode in node.Nodes)
                        {
                            if (childNode.Checked)
                            {
                                sbScript.AppendFormat("ALTER TABLE [{0}] DROP CONSTRAINT [{1}] {2}", TableName, childNode.Text, Environment.NewLine);
                            }
                        }
                    }
                    else
                    {
                        foreach (TreeNode childNode in node.Nodes)
                        {
                            if (childNode.Checked)
                            {
                                sbScript.AppendFormat("ALTER TABLE [{0}] DROP INDEX [{1}] {2}", TableName, childNode.Text, Environment.NewLine);
                            }
                        }
                    }
                }
                if (sbScript.Length > 0)
                {
                    ConstraintScript = sbScript.ToString();
                    btnOk.PerformClick();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnAddUk_Click(object sender, EventArgs e)
        {            
            grpFk.Visible = false;
            grpUk.Visible = true;
            rdIndex.Checked = false;
            rdUk.Checked = false;
            rdIndex.Enabled = true;
            rdUk.Enabled = true;
            chkUnique.Checked = false;
            txtUkName.Text = string.Empty;
            txtUkName.ReadOnly = false;
            dgUks.Rows.Clear();

            btnAddFk.Enabled = false;
            btnAddUk.Enabled = false;            
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {           
            grpFk.Visible = false;
            grpUk.Visible = false;
            btnAddFk.Enabled = true;
            btnAddUk.Enabled = true;            
        }

        private void rdUk_CheckedChanged(object sender, EventArgs e)
        {           
            if (rdUk.Checked)
            {
                chkUnique.Checked = true;
                chkUnique.Enabled = false;
                dgUks.Columns[1].Visible = false;
            }            
        }

        private void rdIndex_CheckedChanged(object sender, EventArgs e)
        {            
            if (rdIndex.Checked)
            {
                chkUnique.Checked = false;
                chkUnique.Enabled = true;
                dgUks.Columns[1].Visible = true;
            }           
        }

        private void dgFks_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {

        }

        private void dgUks_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {

        }

    }
}
